﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using acessaDB;
using Frame;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Estrutura;


namespace Banco
{

    /// <summary>
    /// Summary description for BD_Pagamento
    /// </summary>
    public class BD_Pagamento
    {
        #region variaveis_globais

        acessaDB.conectaDBMSSQLServer sqlServer = new conectaDBMSSQLServer();
        string conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionLocal"];
        string usuario = string.Empty;

        #endregion

        #region construtor

        public BD_Pagamento()
        {
            if (System.Configuration.ConfigurationManager.AppSettings["ambiente"] == "PROD")
                conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionProd"];
        }
        public BD_Pagamento(string usu_Login)
        {
            if (System.Configuration.ConfigurationManager.AppSettings["ambiente"] == "PROD")
                conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionProd"];
            usuario = usu_Login;
        }

        #endregion

        #region metodos

        public System.Data.DataSet _retorna_Pagamento(
                                                string PAGAMENTO_ID,
                                                string MATRICULA_ID,
                                                string TURMA_MENSALIDADE_ID,
                                                string BOLSA_ID,
                                                string PAG_VALORPAGO,
                                                string PAG_VALORMENSALIDADE,
                                                string PAG_MULTA,
                                                string PAG_JUROS,
                                                string PAG_DESCONTO,
                                                string PAG_DTPAMENTO
                                                )
        {

            if (string.IsNullOrEmpty(PAGAMENTO_ID)) PAGAMENTO_ID = "''";
            if (string.IsNullOrEmpty(MATRICULA_ID)) MATRICULA_ID = "''";
            if (string.IsNullOrEmpty(TURMA_MENSALIDADE_ID)) TURMA_MENSALIDADE_ID = "''";
            if (string.IsNullOrEmpty(BOLSA_ID)) BOLSA_ID = "''";
            if (string.IsNullOrEmpty(PAG_VALORPAGO)) PAG_VALORPAGO = "''";
            if (string.IsNullOrEmpty(PAG_VALORMENSALIDADE)) PAG_VALORMENSALIDADE = "''";
            if (string.IsNullOrEmpty(PAG_MULTA)) PAG_MULTA = "''";
            if (string.IsNullOrEmpty(PAG_JUROS)) PAG_JUROS = "''";
            if (string.IsNullOrEmpty(PAG_DESCONTO)) PAG_DESCONTO = "''";
            if (string.IsNullOrEmpty(PAG_DTPAMENTO)) PAG_DTPAMENTO = "''";

            string sql = "SELECT " +
                                  "PAGAMENTO.PAGAMENTO_ID, " +
                                  "PAGAMENTO.MATRICULA_ID, " +
                                  "PAGAMENTO.TURMA_MENSALIDADE_ID, " +
                                  "PAGAMENTO.BOLSA_ID, " +
                                  "PAGAMENTO.PAG_VALORPAGO, " +
                                  "PAGAMENTO.PAG_VALORMENSALIDADE, " +
                                  "PAGAMENTO.PAG_MULTA, " +
                                  "PAGAMENTO.PAG_JUROS, " +
                                  "PAGAMENTO.PAG_DESCONTO, " +
                                  "PAGAMENTO.PAG_DTPAMENTO " +
                            "FROM PAGAMENTO " +
                            "LEFT JOIN BOLSA ON BOLSA.BOLSA_ID = PAGAMENTO.BOLSA_ID " +
                            "LEFT JOIN MATRICULA ON MATRICULA.MATRICULA_ID = PAGAMENTO.MATRICULA_ID " +
                            "WHERE " +
                            "      (PAGAMENTO.PAGAMENTO_ID = " + PAGAMENTO_ID + "  OR " + PAGAMENTO_ID + " = '') AND " +
                            "      (PAGAMENTO.MATRICULA_ID = " + MATRICULA_ID + "  OR " + MATRICULA_ID + " = '') AND " +
                            "      (PAGAMENTO.TURMA_MENSALIDADE_ID = " + TURMA_MENSALIDADE_ID + "  OR " + TURMA_MENSALIDADE_ID + " = '') AND " +
                            "      (PAGAMENTO.BOLSA_ID = " + BOLSA_ID + "  OR " + BOLSA_ID + " = '') AND " +
                            "      (PAGAMENTO.PAG_VALORPAGO = " + PAG_VALORPAGO + "  OR " + PAG_VALORPAGO + " = '') AND " +
                            "      (PAGAMENTO.PAG_VALORMENSALIDADE = " + PAG_VALORMENSALIDADE + "  OR " + PAG_VALORMENSALIDADE + " = '') AND " +
                            "      (PAGAMENTO.PAG_MULTA = " + PAG_MULTA + "  OR " + PAG_MULTA + " = '') AND " +
                            "      (PAGAMENTO.PAG_JUROS = " + PAG_JUROS + "  OR " + PAG_JUROS + " = '') AND " +
                            "      (PAGAMENTO.PAG_DESCONTO = " + PAG_DESCONTO + "  OR " + PAG_DESCONTO + " = '') AND " +
                            "      (PAGAMENTO.PAG_DTPAMENTO = " + PAG_DTPAMENTO + "  OR " + PAG_DTPAMENTO + " = '') ";

            return sqlServer.retornaDataSet(sql, conectionString);;
            

        }


        public List<ES_Pagamento> _obter_Pagamento(
                                                    string PAGAMENTO_ID,
                                                    string MATRICULA_ID,
                                                    string TURMA_MENSALIDADE_ID,
                                                    string BOLSA_ID,
                                                    string PAG_VALORPAGO,
                                                    string PAG_VALORMENSALIDADE,
                                                    string PAG_MULTA,
                                                    string PAG_JUROS,
                                                    string PAG_DESCONTO,
                                                    string PAG_DTPAMENTO,
                                                    string PAG_ANOREFERENCIA,
		                                            string PAG_MESREFERENCIA,
                                                    string PAGAMENTO_TIPO_ID,
                                                    string ALUNO_ID,
                                                    string PES_NOME,
                                                    string ORDER_BY_CLAUSE
                                                )
        {

            if (string.IsNullOrEmpty(PAGAMENTO_ID)) PAGAMENTO_ID = "0"; 
            if (string.IsNullOrEmpty(MATRICULA_ID)) MATRICULA_ID = "0";
            if (string.IsNullOrEmpty(TURMA_MENSALIDADE_ID)) TURMA_MENSALIDADE_ID = "0";
            if (string.IsNullOrEmpty(BOLSA_ID)) BOLSA_ID = "0";
            if (string.IsNullOrEmpty(PAG_VALORPAGO)) PAG_VALORPAGO = "0";
            if (string.IsNullOrEmpty(PAG_VALORMENSALIDADE)) PAG_VALORMENSALIDADE = "0";
            if (string.IsNullOrEmpty(PAG_MULTA)) PAG_MULTA = "0";
            if (string.IsNullOrEmpty(PAG_JUROS)) PAG_JUROS = "0";
            if (string.IsNullOrEmpty(PAG_DESCONTO)) PAG_DESCONTO = "0";
            if (!string.IsNullOrEmpty(PAG_DTPAMENTO)) PAG_DTPAMENTO = Util.retonarAspas(Util.formataData(PAG_DTPAMENTO, "dd/MM/yyyy"));
            if (string.IsNullOrEmpty(PAGAMENTO_TIPO_ID)) PAGAMENTO_TIPO_ID = "0";
            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "0";
            if (string.IsNullOrEmpty(PES_NOME)) PES_NOME = "''"; else PES_NOME = Util.retonarAspas(PES_NOME + "%");
            if (string.IsNullOrEmpty(PAG_ANOREFERENCIA)) PAG_ANOREFERENCIA = "0";
		    if (string.IsNullOrEmpty(PAG_MESREFERENCIA)) PAG_MESREFERENCIA = "0";
            if (string.IsNullOrEmpty(ORDER_BY_CLAUSE)) ORDER_BY_CLAUSE = "PAGAMENTO.PAG_DTPAMENTO DESC ";

            string sql = "SELECT " +
                                    "PAGAMENTO.PAGAMENTO_ID, "+
		                            "PAGAMENTO.MATRICULA_ID, "+
                                    "ISNULL(TURMA.TUR_DESCRICAO,'')+' .:. '+ISNULL(MODALIDADE.MOD_DESCRICAO,'')+' .:. '+ISNULL(NIVEL.NIV_DESCRICAO,'') MAT_MTRDESCRICAO, " +
		                            "ALUNO.ALUNO_ID, "+
		                            "PESSOA.PES_NOME, "+
		                            "PAGAMENTO.TURMA_MENSALIDADE_ID, "+
		                            "PAGAMENTO.BOLSA_ID, "+
                                    "BOLSA.BOL_DESCRICAO, "+
		                            "PAGAMENTO.PAG_VALORPAGO, "+
		                            "PAGAMENTO.PAG_ANOREFERENCIA, "+
                                    "CASE PAGAMENTO.PAG_MESREFERENCIA  " +
                                    "               WHEN 1 THEN 'JANEIRO'  " +
                                    "               WHEN 2 THEN 'FEVEREIRO'  " +
                                    "               WHEN 3 THEN 'MARÇO'  " +
                                    "               WHEN 4 THEN 'ABRIL'  " +
                                    "               WHEN 5 THEN 'MAIO'  " +
                                    "               WHEN 6 THEN 'JUNHO'  " +
                                    "               WHEN 7 THEN 'JULHO'  " +
                                    "               WHEN 8 THEN 'AGOSTO'  " +
                                    "               WHEN 9 THEN 'SETEMBRO'  " +
                                    "               WHEN 10 THEN 'OUTUBRO'  " +
                                    "               WHEN 11 THEN 'NOVEMBRO' 	 " +
                                    "               ELSE 'DEZEMBRO' END PAG_MESREFERENCIA,    " +                                    
		                            "PAGAMENTO.PAG_VALORMENSALIDADE, "+ 
		                            "PAGAMENTO.PAG_MULTA,  "+
		                            "PAGAMENTO.PAG_JUROS,  "+
		                            "PAGAMENTO.PAG_DESCONTO,  "+
		                            "PAGAMENTO.PAG_DTPAMENTO,  "+
                                    "PAGAMENTO.PAGAMENTO_TIPO_ID, " + 
		                            "PAGAMENTO_TIPO.PTO_DESCRICAO " +
                            "FROM PAGAMENTO " +
                            "LEFT JOIN BOLSA ON BOLSA.BOLSA_ID = PAGAMENTO.BOLSA_ID " +
                            "LEFT JOIN MATRICULA ON MATRICULA.MATRICULA_ID = PAGAMENTO.MATRICULA_ID " +
                            "LEFT JOIN TURMA ON (TURMA.TURMA_ID = MATRICULA.TURMA_ID) "+
                            "LEFT JOIN MODALIDADE ON (MODALIDADE.MODALIDADE_ID = MATRICULA.MODALIDADE_ID) " +
                            "LEFT JOIN NIVEL ON (NIVEL.NIVEL_ID = MATRICULA.NIVEL_ID) "+
                            "LEFT JOIN ALUNO ON (MATRICULA.ALUNO_ID = ALUNO.ALUNO_ID OR PAGAMENTO.ALUNO_ID = ALUNO.ALUNO_ID) "+
                            "LEFT JOIN PESSOA ON (PESSOA.PESSOA_ID = ALUNO.PESSOA_ID) "+
                            "LEFT JOIN PAGAMENTO_TIPO ON (PAGAMENTO.PAGAMENTO_TIPO_ID = PAGAMENTO_TIPO.PAGAMENTO_TIPO_ID) " +
                            "WHERE " +
                            "      (PAGAMENTO.PAGAMENTO_ID = " + PAGAMENTO_ID + "  OR " + PAGAMENTO_ID + " = 0) AND " +
                            "      (PAGAMENTO.MATRICULA_ID = " + MATRICULA_ID + "  OR " + MATRICULA_ID + " = 0) AND " +
                            "      (PAGAMENTO.TURMA_MENSALIDADE_ID = " + TURMA_MENSALIDADE_ID + "  OR " + TURMA_MENSALIDADE_ID + " = 0) AND " +
                            "      (PAGAMENTO.BOLSA_ID = " + BOLSA_ID + "  OR " + BOLSA_ID + " = 0) AND " +
                            "      (PAGAMENTO.PAG_VALORPAGO = " + PAG_VALORPAGO + "  OR " + PAG_VALORPAGO + " = 0) AND " +
                            "      (PAGAMENTO.PAG_VALORMENSALIDADE = " + PAG_VALORMENSALIDADE + "  OR " + PAG_VALORMENSALIDADE + " = 0) AND " +
                            "      (PAGAMENTO.PAG_MULTA = " + PAG_MULTA + "  OR " + PAG_MULTA + " = 0) AND " +
                            "      (PAGAMENTO.PAG_JUROS = " + PAG_JUROS + "  OR " + PAG_JUROS + " = 0) AND " +
                            "      (PAGAMENTO.PAG_DESCONTO = " + PAG_DESCONTO + "  OR " + PAG_DESCONTO + " = 0) AND ";

            if (!string.IsNullOrEmpty(PAG_DTPAMENTO))
                sql +=      "      (PAGAMENTO.PAG_DTPAMENTO = " + PAG_DTPAMENTO + "  OR " + PAG_DTPAMENTO + " = '') ";

            sql += "      (PAGAMENTO.PAGAMENTO_TIPO_ID = " + PAGAMENTO_TIPO_ID + "  OR " + PAGAMENTO_TIPO_ID + " = 0) AND " +
                            "      (ALUNO.ALUNO_ID = " + ALUNO_ID + "  OR " + ALUNO_ID + " = 0) AND " +
                            "      (PESSOA.PES_NOME LIKE " + PES_NOME + "  OR " + PES_NOME + " = '') AND" +
                            "      (PAGAMENTO.PAG_ANOREFERENCIA = " + PAG_ANOREFERENCIA + "  OR " + PAG_ANOREFERENCIA + " = 0) AND " +
                            "      (PAGAMENTO.PAG_MESREFERENCIA = " + PAG_MESREFERENCIA + "  OR " + PAG_MESREFERENCIA + " = 0) " +
                            "ORDER BY " + ORDER_BY_CLAUSE;

            List<ES_Pagamento> lista = new List<ES_Pagamento>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    ES_Pagamento item = new ES_Pagamento();
                    item.PAGAMENTO_ID = dr["PAGAMENTO_ID"].ToString(); 
		            item.MATRICULA_ID = dr["MATRICULA_ID"].ToString();
                    item.MAT_MTRDESCRICAO = dr["MAT_MTRDESCRICAO"].ToString();
		            item.ALUNO_ID = dr["ALUNO_ID"].ToString();
		            item.PES_NOME = dr["PES_NOME"].ToString();
		            item.TURMA_MENSALIDADE_ID = dr["TURMA_MENSALIDADE_ID"].ToString(); 
		            item.BOLSA_ID = dr["BOLSA_ID"].ToString();
                    item.BOL_DESCRICAO = dr["BOL_DESCRICAO"].ToString(); 
		            item.PAG_VALORPAGO = Util.formataReal2Casas(dr["PAG_VALORPAGO"].ToString()); 
		            item.PAG_ANOREFERENCIA = dr["PAG_ANOREFERENCIA"].ToString();
		            item.PAG_MESREFERENCIA = dr["PAG_MESREFERENCIA"].ToString();
		            item.PAG_VALORMENSALIDADE = Util.formataReal2Casas(dr["PAG_VALORMENSALIDADE"].ToString()); 
		            item.PAG_MULTA = Util.formataReal2Casas(dr["PAG_MULTA"].ToString()); 
		            item.PAG_JUROS = Util.formataReal2Casas(dr["PAG_JUROS"].ToString()); 
		            item.PAG_DESCONTO = Util.formataReal2Casas(dr["PAG_DESCONTO"].ToString());
                    if (!string.IsNullOrEmpty(dr["PAG_DTPAMENTO"].ToString()))
                        item.PAG_DTPAMENTO = Util.formataData(dr["PAG_DTPAMENTO"].ToString(),"dd/MM/yyyy"); 
		            item.PAGAMENTO_TIPO_ID = dr["PAGAMENTO_TIPO_ID"].ToString();
                    item.PTO_DESCRICAO = dr["PTO_DESCRICAO"].ToString();
                    item.PAG_DTPAMENTO = dr["PAG_DTPAMENTO"].ToString().Replace("00:00:00", "").Trim();
                    lista.Add(item);

                }
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }


        public List<ES_Data> _obter_AnoPagamento()
        {

            string sql = "SELECT DATEPART(YEAR,CURRENT_TIMESTAMP) ANO " +
                         "   UNION ALL "+
                         "   SELECT DATEPART(YEAR,CURRENT_TIMESTAMP)-1 ANO " +
                         "   UNION ALL " +
                         "   SELECT DATEPART(YEAR,CURRENT_TIMESTAMP)-2 ANO ";

            List<ES_Data> lista = new List<ES_Data>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    ES_Data item = new ES_Data();
                    item.ANO = dr["ANO"].ToString();
                    item.ANO_DESCRICAO = dr["ANO"].ToString();                    
                    lista.Add(item);

                }
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }

        public List<ES_Ano> _obter_Ano_Pagamento_Aluno(string MATRICULA_ID)
        {
            
            if (string.IsNullOrEmpty(MATRICULA_ID))
                return new List<ES_Ano>();

            string sql = "SELECT DISTINCT (DATEPART(YEAR,PAG_DTPAMENTO)) ANO FROM PAGAMENTO "+
                         "WHERE MATRICULA_ID = "+MATRICULA_ID+" ORDER BY ANO";

            List<ES_Ano> lista = new List<ES_Ano>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    ES_Ano item = new ES_Ano();
                    item.ANO = dr["ANO"].ToString();
                    item.ANO_DESC = dr["ANO"].ToString();
                    
                    lista.Add(item);
                }
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }

        public List<ES_Pagamento> _obter_Pagamento_Conciliacao(
                                                                string ALUNO_ID,
                                                                string MATRICULA_ID,
                                                                string MENSALIDADES_EM_ABERTO,
                                                                string PAG_ANOREFERENCIA,
                                                                string PAG_MESREFERENCIA,
                                                                string SOMENTE_MENSALIDADES_EM_ABERTO = "N"
                                                            )
        {

            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "0";
            if (string.IsNullOrEmpty(MATRICULA_ID)) MATRICULA_ID = "0";
            if (string.IsNullOrEmpty(MENSALIDADES_EM_ABERTO)) MENSALIDADES_EM_ABERTO = "S"; 
            //
            if (string.IsNullOrEmpty(PAG_ANOREFERENCIA)) PAG_ANOREFERENCIA = "0";
            if (string.IsNullOrEmpty(PAG_MESREFERENCIA)) PAG_MESREFERENCIA = "0";

            string sql = "SELECT MATRICULA_ID, "+
		                 "       BOLSA_ID, "+
		                 "       CASE WHEN DATA_REFERENCIAL <> DATA_CALCULADA THEN NULL ELSE PAG_VALORPAGO END PAG_VALORPAGO, "+
                         "       CASE WHEN DATA_REFERENCIAL <> DATA_CALCULADA THEN 'Em aberto' ELSE 'Paga' END PAG_SITUACAO, "+
		                 "       PAG_VALORMENSALIDADE, "+
		                 "       PAG_MULTA, "+
		                 "       PAG_JUROS, "+
		                 "       PAG_DESCONTO, "+
                         "       CASE WHEN DATA_REFERENCIAL <> DATA_CALCULADA THEN NULL ELSE PAG_DTPAMENTO END PAG_DTPAMENTO,  " +
		                 "       PAG_ANOREFERENCIA, "+ 
		                 "       PAG_MESREFERENCIA, "+	
		                 "       PAGAMENTO_TIPO_ID, "+
		                 "       EVENTO_ID, "+
		                 "       MATERIAL_ID, "+
		                 "       DATA_REFERENCIAL, "+
		                 "       NUMERO_MENSALIDADE, "+
		                 "       MAT_DTINCLUSAO, "+
                         "       DATA_CALCULADA, " +
                         "       ANO_CALCULADO, " +
                         "       MES_CALCULADO, " +
                         "       CASE MES_CALCULADO  " +
		                 "               WHEN 1 THEN 'JANEIRO'  " +
		                 "               WHEN 2 THEN 'FEVEREIRO'  " +
		                 "               WHEN 3 THEN 'MARÇO'  " +
		                 "               WHEN 4 THEN 'ABRIL'  " +
		                 "               WHEN 5 THEN 'MAIO'  " +
		                 "               WHEN 6 THEN 'JUNHO'  " +
		                 "               WHEN 7 THEN 'JULHO'  " +
		                 "               WHEN 8 THEN 'AGOSTO'  " +
		                 "               WHEN 9 THEN 'SETEMBRO'  " +
		                 "               WHEN 10 THEN 'OUTUBRO'  " +
		                 "               WHEN 11 THEN 'NOVEMBRO' 	 " +
                         "               ELSE 'DEZEMBRO' END MES_CALCULADOEXT    " +
                         "FROM DBO.FN_MENSALIDADES_CONCILIACAO("+Util.retonarNull(ALUNO_ID)+","+Util.retonarNull(MATRICULA_ID)+") "+
                         "WHERE 1=1 ";

            if (SOMENTE_MENSALIDADES_EM_ABERTO == "S")
                sql += "AND ISNULL(DATA_REFERENCIAL,CURRENT_TIMESTAMP) <> DATA_CALCULADA ";
            else if (MENSALIDADES_EM_ABERTO == "N")
                sql += "AND ISNULL(DATA_REFERENCIAL,CURRENT_TIMESTAMP) = DATA_CALCULADA ";
            

            if (PAG_ANOREFERENCIA != "0")
                sql += "AND PAG_ANOREFERENCIA = " + PAG_ANOREFERENCIA;

            if (PAG_MESREFERENCIA != "0")
                sql += "AND PAG_MESREFERENCIA = " + PAG_MESREFERENCIA;

            sql += " ORDER BY NUMERO_MENSALIDADE DESC ";

            List<ES_Pagamento> lista = new List<ES_Pagamento>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    ES_Pagamento item = new ES_Pagamento();
                    item.MATRICULA_ID = dr["MATRICULA_ID"].ToString(); 		
		            item.BOLSA_ID = dr["BOLSA_ID"].ToString(); 
		            item.PAG_VALORPAGO = dr["PAG_VALORPAGO"].ToString();
                    item.PAG_SITUACAO = dr["PAG_SITUACAO"].ToString(); 	
		            item.PAG_VALORMENSALIDADE = dr["PAG_VALORMENSALIDADE"].ToString(); 
		            item.PAG_MULTA = dr["PAG_MULTA"].ToString(); 
		            item.PAG_JUROS = dr["PAG_JUROS"].ToString(); 
		            item.PAG_DESCONTO = dr["PAG_DESCONTO"].ToString();
                    item.PAG_DTPAMENTO = Util.formataData(dr["PAG_DTPAMENTO"].ToString(), "dd/MM/yyyy", true); 
		            item.PAG_ANOREFERENCIA = dr["PAG_ANOREFERENCIA"].ToString(); 
		            item.PAG_MESREFERENCIA = dr["PAG_MESREFERENCIA"].ToString();                    
		            item.PAGAMENTO_TIPO_ID = dr["PAGAMENTO_TIPO_ID"].ToString(); 
		            item.EVENTO_ID = dr["EVENTO_ID"].ToString(); 
		            item.MATERIAL_ID = dr["MATERIAL_ID"].ToString();
                    item.DATA_REFERENCIAL = Util.formataData(dr["DATA_REFERENCIAL"].ToString(), "dd/MM/yyyy", true); 
		            item.NUMERO_MENSALIDADE = dr["NUMERO_MENSALIDADE"].ToString();
                    item.MAT_DTINCLUSAO = Util.formataData(dr["MAT_DTINCLUSAO"].ToString(), "dd/MM/yyyy", true);
                    item.DATA_CALCULADA = Util.formataData(dr["DATA_CALCULADA"].ToString(), "dd/MM/yyyy", true);
                    item.ANO_CALCULADO = dr["ANO_CALCULADO"].ToString();
                    item.MES_CALCULADO = dr["MES_CALCULADO"].ToString();
                    item.MES_CALCULADOEXT = dr["MES_CALCULADOEXT"].ToString();
                    item.ANOMES_CALCULADO = item.ANO_CALCULADO + " - " + item.MES_CALCULADOEXT;

                    lista.Add(item);

                }
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }

        public ES_Pagamento _obter_Pagamento_by_id(
                                                    string PAGAMENTO_ID,
                                                    string MATRICULA_ID = "",
                                                    string ALUNO_ID = "",
                                                    string PAG_ANOREFERENCIA = "",
                                                    string PAG_MESREFERENCIA = ""
                                                )
        {

            if (string.IsNullOrEmpty(PAGAMENTO_ID)) PAGAMENTO_ID = "0";
            if (string.IsNullOrEmpty(MATRICULA_ID) || MATRICULA_ID == "-1") MATRICULA_ID = "0";
            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "0";
            if (string.IsNullOrEmpty(PAG_ANOREFERENCIA)) PAG_ANOREFERENCIA = "0";
            if (string.IsNullOrEmpty(PAG_MESREFERENCIA)) PAG_MESREFERENCIA = "0";

            string sql = "SELECT " +
                                 "PAGAMENTO_ID,  " +
                                 "PAGAMENTO.MATRICULA_ID,  " +
                                 "PAGAMENTO.TURMA_MENSALIDADE_ID,  " +
                                 "PAGAMENTO.BOLSA_ID,  " +
                                 "PAGAMENTO.PAG_VALORPAGO,  " +
                                 "PAGAMENTO.PAG_VALORMENSALIDADE,  " +
                                 "PAGAMENTO.PAG_MULTA,  " +
                                 "PAGAMENTO.PAG_JUROS,  " +
                                 "PAGAMENTO.PAG_DESCONTO,  " +
                                 "PAGAMENTO.PAG_DTPAMENTO,  " +
                                 "PAGAMENTO.PAG_ANOREFERENCIA,  " +
                                 "PAGAMENTO.PAG_MESREFERENCIA,  " +
                                 "PAGAMENTO.PAG_EXCLUIDO,  " +
                                 "PAGAMENTO.PAGAMENTO_TIPO_ID, " +
                                 "PAGAMENTO.EVENTO_ID,  " +
                                 "PAGAMENTO.MATERIAL_ID,  " +
                                 "PAGAMENTO.PAG_OBSERVACAO,  " +
                                 "PAGAMENTO.FORMA_PAGAMENTO_ID,  " +
                                 "PAGAMENTO.ALUNO_MATERIAL_ID,  " +
                                 "PAGAMENTO.ALUNO_EVENTO_ID,  " +
                                 "PAGAMENTO.ALUNO_ID " +
                            "FROM PAGAMENTO " +
                            "LEFT JOIN BOLSA ON BOLSA.BOLSA_ID = PAGAMENTO.BOLSA_ID " +
                            "LEFT JOIN MATRICULA ON MATRICULA.MATRICULA_ID = PAGAMENTO.MATRICULA_ID " +
                            "WHERE " +
                            "      (PAGAMENTO.PAGAMENTO_ID = " + PAGAMENTO_ID + "  OR " + PAGAMENTO_ID + " = 0) AND ";
            
            if (MATRICULA_ID != "0")
                sql += "      (PAGAMENTO.MATRICULA_ID = " + MATRICULA_ID + "  OR " + MATRICULA_ID + " = 0) AND ";
            else
                sql += "      (MATRICULA.ALUNO_ID = " + ALUNO_ID + "  OR " + ALUNO_ID + " = 0) AND ";
            
            sql +=
                            "      (PAGAMENTO.PAG_ANOREFERENCIA = " + PAG_ANOREFERENCIA + "  OR " + PAG_ANOREFERENCIA + " = 0) AND" +
                            "      (PAGAMENTO.PAG_MESREFERENCIA = " + PAG_MESREFERENCIA + "  OR " + PAG_MESREFERENCIA + " = 0) ";

            ES_Pagamento item = new ES_Pagamento();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    item.PAGAMENTO_ID = dr["PAGAMENTO_ID"].ToString();
                    item.MATRICULA_ID = dr["MATRICULA_ID"].ToString();
                    item.TURMA_MENSALIDADE_ID = dr["TURMA_MENSALIDADE_ID"].ToString();
                    item.BOLSA_ID = dr["BOLSA_ID"].ToString();
                    item.PAG_VALORPAGO = dr["PAG_VALORPAGO"].ToString();
                    item.PAG_VALORMENSALIDADE = dr["PAG_VALORMENSALIDADE"].ToString();
                    item.PAG_MULTA = dr["PAG_MULTA"].ToString();
                    item.PAG_JUROS = dr["PAG_JUROS"].ToString();
                    item.PAG_DESCONTO = dr["PAG_DESCONTO"].ToString();
                    item.PAG_DTPAMENTO = dr["PAG_DTPAMENTO"].ToString();

                }
                return item;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }


        public void _inserir_Pagamento(
                                                string PAGAMENTO_ID,
                                                string MATRICULA_ID,
                                                string TURMA_MENSALIDADE_ID,
                                                string BOLSA_ID,
                                                string PAG_VALORPAGO,
                                                string PAG_VALORMENSALIDADE,
                                                string PAG_MULTA,
                                                string PAG_JUROS,
                                                string PAG_DESCONTO,
                                                string PAG_DTPAMENTO,
                                                string PAG_ANOREFERENCIA,
                                                string PAG_MESREFERENCIA,
                                                string PAG_EXCLUIDO = "0",
                                                string PAGAMENTO_TIPO_ID = "1",
                                                string EVENTO_ID = "",
                                                string MATERIAL_ID = "",
                                                string PAG_OBSERVACAO = "",
                                                string ALUNO_ID = "",
                                                string ALUNO_MATERIAL_ID = "",
                                                string ALUNO_EVENTO_ID = ""

                                                )
        {

            string sql = " INSERT INTO PAGAMENTO " +
                         "      ( " +
                         "          MATRICULA_ID, " +
                         "          TURMA_MENSALIDADE_ID, " +
                         "          BOLSA_ID, " +
                         "          PAG_VALORPAGO, " +
                         "          PAG_VALORMENSALIDADE, " +
                         "          PAG_MULTA, " +
                         "          PAG_JUROS, " +
                         "          PAG_DESCONTO, " +
                         "          PAG_DTPAMENTO, " +
                         "          PAG_ANOREFERENCIA, " +
                         "          PAG_MESREFERENCIA, " +
                         "          PAG_EXCLUIDO, " +
                         "          PAGAMENTO_TIPO_ID, " +
                         "          EVENTO_ID, " +
                         "          MATERIAL_ID, " +
                         "          PAG_OBSERVACAO, " +
                         "          ALUNO_ID, " +
                         "          ALUNO_MATERIAL_ID, " +
                         "          ALUNO_EVENTO_ID " +
                 "      )" +
                 "  VALUES " +
                 "      ( " +
                              Util.retonarNull(MATRICULA_ID) + ", " +
                              Util.retonarNull(TURMA_MENSALIDADE_ID) + ", " +
                              Util.retonarNull(BOLSA_ID) + ", " +
                              Util.retonarNull(PAG_VALORPAGO.Replace(",",".")) + ", " +
                              Util.retonarNull(PAG_VALORMENSALIDADE.Replace(",", ".")) + ", " +
                              Util.retonarNull(PAG_MULTA) + ", " +
                              Util.retonarNull(PAG_JUROS) + ", " +
                              Util.retonarNull(PAG_DESCONTO) + ", " +
                              Util.retonarAspas(Util.formataData(PAG_DTPAMENTO, "dd/MM/yyyy", true), true) + ", " +
                              Util.retonarNull(PAG_ANOREFERENCIA) +", " +
                              Util.retonarNull(PAG_MESREFERENCIA) +", " +
                              Util.retonarNull(PAG_EXCLUIDO) +", " +
                              Util.retonarNull(PAGAMENTO_TIPO_ID) +", " +
                              Util.retonarNull(EVENTO_ID) +", " +
                              Util.retonarNull(MATERIAL_ID) + ", " +
                              Util.retonarAspas(PAG_OBSERVACAO, true) + ", " +
                              Util.retonarNull(ALUNO_ID) + ", " +
                              Util.retonarNull(ALUNO_MATERIAL_ID) + ", " +
                              Util.retonarNull(ALUNO_EVENTO_ID) +
                 "      )";
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }


        public void _inserir_Pagamento_Matriculas(  
                                                    string ALUNO_ID,
                                                    string BOLSA_ID,
                                                    string PAG_VALORPAGO,
                                                    string PAG_VALORMENSALIDADE,
                                                    string PAG_MULTA,
                                                    string PAG_JUROS,
                                                    string PAG_DESCONTO,
                                                    string PAG_DTPAMENTO,
                                                    string PAG_ANOREFERENCIA,
                                                    string PAG_MESREFERENCIA,
                                                    string PAG_OBSERVACAO,
                                                    //
                                                    string FORMA_PAGAMENTO_ID,
                                                    string PDT_PARCELA,
                                                    string PDT_DATAPAMENTO,
                                                    string PDT_BANDEIRA,
                                                    string PDT_NUMEROCHEQUE
                                       
                                                )
        {
            int descAplicado = 0;            

            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "0";
            if (ALUNO_ID != "0")
            {
                string sql = "SELECT M.MATRICULA_ID, " +
                             "           M.ALUNO_ID, " +
                             "           M.TURMA_ID,	 " +
                             "           T.TUR_DESCRICAO, " +
                             "           M.BOLSA_ID, " +
                             "           B.BOL_DESCRICAO, " +
                             "           M.MAT_DTINCLUSAO, " +
                             "           M.MAT_DTEXCLUSAO, " +
                             "           M.MODALIDADE_ID, " +
                             "           D.MOD_DESCRICAO, " +
                             "           M.MAT_VRMENSALIDADE, " +
                             "           M.BOLSA_ID, "+
                             "           CASE  " +
                             "               WHEN B.BOL_DESCONTOPERC IS NOT NULL THEN M.MAT_VRMENSALIDADE * (1-(B.BOL_DESCONTOPERC/100)) " +
                             "               WHEN B.BOL_DESCONTOVALOR IS NOT NULL THEN M.MAT_VRMENSALIDADE - BOL_DESCONTOVALOR " +
                             "               ELSE M.MAT_VRMENSALIDADE END AS MAT_VRMENSALIDADEDESC, " +
                             "           M.MAT_STEXCLUIDO, " +
                             "           S.SAL_DESCRICAO, " +
                             "           TN.TUR_DESCRICAO TURNO_DESCRICAO, " +
                             "           T.TUR_VALORREFERENCIA " +
                             "   FROM MATRICULA M  " +
                             "   INNER JOIN TURMA T ON (T.TURMA_ID = M.TURMA_ID) " +
                             "   LEFT JOIN SALA S ON (S.SALA_ID = T.SALA_ID) " +
                             "   LEFT JOIN TURNO TN ON (TN.TURNO_ID = T.TURNO_ID) " +
                             "   LEFT JOIN BOLSA B ON (B.BOLSA_ID = M.BOLSA_ID) " +
                             "   LEFT JOIN MODALIDADE D ON (D.MODALIDADE_ID = M.MODALIDADE_ID) " +
                             " WHERE " +
                             "      (M.ALUNO_ID = " + ALUNO_ID + "  OR " + ALUNO_ID + " = 0) " +
                             "ORDER BY M.BOLSA_ID DESC";
                /*============================================================================================================
                 obter o registro que possui bolsa associada pois o desconto será lançado em uma única matrícula
                        preferencialmente aquela que possui bolsa/desconto associada
                ============================================================================================================*/

                ES_Matricula item = new ES_Matricula();               
                SqlConnection sqlConexao = new SqlConnection(conectionString);
                SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
                try
                {
                    while (dr.Read())
                    {                                               
                        //Para cada item no dataReader .:. efetuar um pagamento
                        _inserir_Pagamento(null,
                                           dr["MATRICULA_ID"].ToString(),
                                           dr["TURMA_ID"].ToString(),
                                           BOLSA_ID,
                                           dr["MAT_VRMENSALIDADEDESC"].ToString(),
                                           dr["MAT_VRMENSALIDADE"].ToString(),
                                           PAG_MULTA,
                                           PAG_JUROS,
                                           (descAplicado == 0 )? PAG_DESCONTO : "NULL",
                                           PAG_DTPAMENTO,
                                           PAG_ANOREFERENCIA,
                                           PAG_MESREFERENCIA,
                                           "0",
                                           "1",
                                           "",
                                           "",
                                           PAG_OBSERVACAO);

                        //recuperar dados do pagamento
                        ES_Pagamento pagamento = new BD_Pagamento()._obter_Pagamento_by_id(string.Empty,
                                                                                        dr["MATRICULA_ID"].ToString(),
                                                                                        string.Empty,
                                                                                        PAG_ANOREFERENCIA,
                                                                                        PAG_MESREFERENCIA);
                        if (!string.IsNullOrEmpty(pagamento.PAGAMENTO_ID))
                        {
                            new BD_Pagamento_detalhe()._inserir_Pagamento_detalhe(string.Empty,
                                                                                  pagamento.PAGAMENTO_ID,
                                                                                  FORMA_PAGAMENTO_ID,
                                                                                  PDT_PARCELA,
                                                                                  PDT_DATAPAMENTO,
                                                                                  PDT_BANDEIRA,
                                                                                  PDT_NUMEROCHEQUE);
                        }
                        descAplicado++;

                    }
                
                }
                finally
                {
                    sqlServer.fecharConexao(sqlConexao);
                }
            }
        }


        public void _atualizar_Pagamento(
                                                string PAGAMENTO_ID,
                                                string MATRICULA_ID,
                                                string TURMA_MENSALIDADE_ID,
                                                string BOLSA_ID,
                                                string PAG_VALORPAGO,
                                                string PAG_VALORMENSALIDADE,
                                                string PAG_MULTA,
                                                string PAG_JUROS,
                                                string PAG_DESCONTO,
                                                string PAG_DTPAMENTO
                                                )
        {

            string sql = " UPDATE PAGAMENTO SET " +
                         "      MATRICULA_ID = " + Util.retonarAspas(MATRICULA_ID, true) + ", " +
                         "      TURMA_MENSALIDADE_ID = " + Util.retonarAspas(TURMA_MENSALIDADE_ID, true) + ", " +
                         "      BOLSA_ID = " + Util.retonarAspas(BOLSA_ID, true) + ", " +
                         "      PAG_VALORPAGO = " + Util.retonarAspas(PAG_VALORPAGO, true) + ", " +
                         "      PAG_VALORMENSALIDADE = " + Util.retonarAspas(PAG_VALORMENSALIDADE, true) + ", " +
                         "      PAG_MULTA = " + Util.retonarAspas(PAG_MULTA, true) + ", " +
                         "      PAG_JUROS = " + Util.retonarAspas(PAG_JUROS, true) + ", " +
                         "      PAG_DESCONTO = " + Util.retonarAspas(PAG_DESCONTO, true) + ", " +
                         "      PAG_DTPAMENTO = " + Util.retonarAspas(PAG_DTPAMENTO, true) +
                         " WHERE PAGAMENTO_ID =  " + PAGAMENTO_ID;
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }



        public void _excluir_Pagamento(string PAGAMENTO_ID)
        {
            string sql = "DELETE FROM PAGAMENTO WHERE PAGAMENTO_ID = " + PAGAMENTO_ID;
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }



        #endregion
    }
}
